﻿using System.Data;
using System;
using System.Data.SqlClient;
using System.Configuration;

namespace WebAPI.Utils
{
    public class SqlHelper
    {
        private static string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["dh_web"].ConnectionString;
        
        public void Conntent()
        {
            var connectionString = "Data Source=(local);Initial Catalog=Northwind;Integrated Security=true";

            // Provide the query string with a parameter placeholder.
            var queryString =
                "SELECT ProductID, UnitPrice, ProductName from dbo.products "
                + "WHERE UnitPrice > @pricePoint "
                + "ORDER BY UnitPrice DESC;";

            // Specify the parameter value.
            int paramValue = 5;

            // Create and open the connection in a using block. This
            // ensures that all resources will be closed and disposed
            // when the code exits.
            using SqlConnection connection = new SqlConnection(connectionString);
            // Create the Command and Parameter objects.
            var command = new SqlCommand(queryString, connection);
            command.Parameters.AddWithValue("@pricePoint", paramValue);

            // Open the connection in a try/catch block.
            // Create and execute the DataReader, writing the result
            // set to the console window.
            try
            {
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Console.WriteLine("\t{0}\t{1}\t{2}",
                        reader[0], reader[1], reader[2]);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            Console.ReadLine();
        }
        
        /// <summary>
        /// 查询所有返回实体集合
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static object SqlQuery(string sql)
        {
            return null;
        }
        
        /// <summary>
        /// 查询第一条记录
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static object SqlQuerySingle(string sql)
        {
            return null;
        }
        
        /// <summary>
        /// 获取首行首列
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static object GetScalar(string sql)
        {
            return GetScalar(sql, null);
        }
        
        public static object GetScalar(string sql, params SqlParameter[] values)
        {
            using var connection = new SqlConnection(connectionString);
            connection.Open();
            var cmd = new SqlCommand(sql, connection);
            cmd.Parameters.AddRange(values);
            return cmd.ExecuteScalar();
        }

        /// <summary>
        /// 获取首行首列
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static string GetString(string sql)
        {
            return GetScalar(sql).ToString();
        }
        
        /// <summary>
        /// 获取首行首列
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int GetInt(string sql)
        {
            return Convert.ToInt32(GetScalar(sql));
        }
        
        /// <summary>
        /// 获取首行首列
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static long GetLong(string sql)
        {
            return Convert.ToInt64(GetScalar(sql));
        }
        
        /// <summary>
        /// 获取首行首列
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static double GetDouble(string sql)
        {
            return Convert.ToDouble(GetScalar(sql));
        }
        
        /// <summary>
        /// 获取首行首列
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static decimal GetDecimal(string sql)
        {
            return Convert.ToDecimal(GetScalar(sql));
        }
        
        /// <summary>
        /// 获取首行首列
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DateTime GetDateTime(string sql)
        {
            return Convert.ToDateTime(GetScalar(sql));
        }
        
        /// <summary>
        /// 读取DR
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static SqlDataReader GetDataReader(string sql)
        {
            return GetDataReader(sql, null);
        }

        /// <summary>
        /// 读取DR
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public static SqlDataReader GetDataReader(string sql, params SqlParameter[] values)
        {
            var connection = new SqlConnection(connectionString);
            connection.Open();
            var cmd = new SqlCommand(sql, connection);
            cmd.Parameters.AddRange(values);
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

        /// <summary>
        /// 查询所有
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataTable GetDataTable(string sql)
        {
            return GetDataTable(sql, null);
        }

        /// <summary>
        /// 查询所有
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public static DataTable GetDataTable(string sql, params SqlParameter[] values)
        {
            return GetDataSetAll(sql, values).Tables[0];
        }
        
        /// <summary>
        /// 获取多个结果集
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public static DataSet GetDataSetAll(string sql, params SqlParameter[] values)
        {
            using var connection = new SqlConnection(connectionString);
            var ds = new DataSet();
            var cmd = new SqlCommand(sql, connection);
            cmd.Parameters.AddRange(values);
            var da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            return ds;
        }
        
        /// <summary>
        /// 返回受影响行数
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int ExecuteCommand(string sql)
        {
            return ExecuteCommand(sql, null);
        }
        
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public void AddBatch(string tableName, DataTable table)
        {
            using var connection = new SqlConnection(connectionString);
            connection.Open();
            using var bulkCopy = new SqlBulkCopy(connection);
            bulkCopy.DestinationTableName = tableName; // 例如 "dbo.MyEntities"
            bulkCopy.BatchSize = 1000; // 可以根据需要调整批次大小
            bulkCopy.BulkCopyTimeout = 600; // 设置超时时间（秒）
            bulkCopy.WriteToServer(table); // 将数据写入服务器
        }
        
        /// <summary>
        /// 返回受影响行数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public static int ExecuteCommand(string sql, params SqlParameter[]? values)
        {
            using var connection = new SqlConnection(connectionString);
            connection.Open();
            var cmd = new SqlCommand(sql, connection);
            if (values != null)
            {
                cmd.Parameters.AddRange(values);
            }
            return cmd.ExecuteNonQuery();
        }
    }
}
